package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.*;

public class DbOutgoingOrder {

private  Connection con;
	
	public DbOutgoingOrder()
	{
		//Constructer of DbOutgoingOrder where the connection is being initialized.
		con = DbConnection.getInstance().getDBcon();
	}
	
	public ArrayList<OutgoingOrder> getAllOrders() {
		//Returns an arrayList with all orders.
		return miscWhere("");
	}

	public OutgoingOrder findOrder(int orderId) {
		//Finds an OutgoingOrder by calling the singleWhere-method using the my using the orderId as wClause
		String wClause = "  orderId = '" + orderId + "'";
        return singleWhere(wClause);
	}

	public int insertOrder(OutgoingOrder oO) throws Exception {
		//Inserts an OutgoingOrder-row by creating a query with the IncomingOrder-object
		int nextOrderId = GetMax.getMaxId("Select max(orderId) from OutgoingOrder");
        nextOrderId = nextOrderId + 1;
        System.out.println("next orderId = " +  nextOrderId);
  
       int rc = -1;
	   String queryOrder="INSERT INTO OutgoingOrder(orderId)  VALUES('"+
			   nextOrderId  + "','"  +
			   oO.getS().getSupplierId()  + "','"  +
			   oO.getC().getCustomerId()  + "','"  +
			   oO.getDate()  + "')";
	   
	   try{
	    	  System.out.println("insert : " + queryOrder);
	          Statement stmt = con.createStatement();
	          stmt.setQueryTimeout(5);
	     	  rc = stmt.executeUpdate(queryOrder);
	          stmt.close();          
	      }
	   catch(SQLException ex){
	          System.out.println("OutgoingOrder is not inserted correct");
	          throw new Exception ("OutgoingOrder is not inserted correct");
	      }
	  
      
       return(nextOrderId);
	}

	public int updateOrder(OutgoingOrder oObj) {
		// Updates the order-row by creating a query, using the OutgoingOrder object and the orderId.
		OutgoingOrder oO = oObj;
		int rc = -1;
		
		String query="UPDATE OutgoingOrder SET "+
			 	  "supplierId ='"+ oO.getS().getSupplierId() + "', " +
			          " WHERE orderId = '"+ oO.getOrderId() + "'";
	                System.out.println("Update query:" + query);
	  		try{ 
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 	 	rc = stmt.executeUpdate(query);

		 	 	stmt.close();
			}
		 	catch(Exception ex){
		 	 	System.out.println("Update exception in OutgoingOrder db: "+ex);
		  	}
			return(rc);
	}
	
	public int delete(int orderId) {
		//Delete a row, by using the orderId.
		int rc=-1;
		  
	 	String queryOrder="DELETE FROM OutgoingOrder WHERE orderId = '" +
				orderId + "'";
                System.out.println(queryOrder);
	  	try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(queryOrder);
	 	  	stmt.close();
  		}
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in OutgoingOrder db: "+ex);
   	        }
	  	
		return(rc);
	}

	private OutgoingOrder singleWhere(String wClause)
	{
		//Creates a single order by calling the buildQuery and then the buildOrder()-method
		ResultSet results;
		OutgoingOrder oO = new OutgoingOrder();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            oO = buildOrder(results);
                            
                            stmt.close();
			}
                        else{ 
                            oO = null;
                        }
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return oO;
	}
	
	private ArrayList<OutgoingOrder> miscWhere(String wClause)
	{
		//Creates several orders by calling the buildQuery and then the buildOrder()-method
        ResultSet results;
	    ArrayList<OutgoingOrder> list = new ArrayList<OutgoingOrder>();	
		
	    String query =  buildQuery(wClause);
  
        try{
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	
		while( results.next() ){
			 OutgoingOrder oO = new OutgoingOrder();
	     	 oO = buildOrder(results);
             list.add(oO);	
			}
	             stmt.close();	
		}
            
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}	
	
	private String buildQuery(String wClause)
	{
		//Builds the query, by using the wClause
	    String query="SELECT orderId, supplierId, customerId, date FROM OutgoingOrder";
	    System.out.println(""+query);
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	private OutgoingOrder buildOrder(ResultSet results)
    {   
		//Builds an order by using the results.
		OutgoingOrder oO = new OutgoingOrder();
        try{
        	  oO.setOrderId(results.getString("orderId"));
        	  oO.setS(new Supplier(results.getInt("supplierId")));
        	  oO.setC(new Customer(results.getInt("customerId")));
        	  oO.setDate(results.getString("date"));
        }
       catch(Exception e)
       {
           System.out.println("error in building the outgoingOrder object");
       }
       return oO;
    }	
	
}